package org.jeecg.modules.cascv.image.util;

import cn.hutool.db.ds.simple.SimpleDataSource;
import cn.hutool.db.sql.SqlExecutor;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.sax.handler.RowHandler;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import java.io.IOException;
import java.sql.Connection;
import java.util.List;


//根据xlsx文件内容 添加、修改数据库数据
@Slf4j
public class TestReadExcel {

    public static void main(String[] args){

        //通过sheet编号获取
        ExcelUtil.read07BySax("D:\\test2.xlsx", 0, new RowHandler() {
            @SneakyThrows
            @Override
            public void handle(int sheetIndex, int rowIndex, List<Object> rowList) {

                String name = rowList.toString();
                name = name.split(",")[2].trim();
                System.out.println("name = " + name);
                String componentName = rowList.toString();
                componentName = componentName.split(",")[5].trim();
                System.out.println("componentName = " + componentName);
                update(name, componentName);
            }
        });


    }

    public static void update(String names, String componentName) throws IOException {
        SimpleDataSource ds = null;
        try {
            ds = new SimpleDataSource("jdbc:mysql://127.0.0.1:3306/jeecg-boot?useUnicode=true&characterEncoding=UTF-8" +
                    "&relaxAutoCommit=true&zeroDateTimeBehavior=convertToNull", "root", "sangda-!_2019*-");
            Connection connection = ds.getConnection();
            //      List<Entity> entityList = SqlExecutor.query(connection, "select * from sys_permission", new EntityListHandler());
            String sql = "UPDATE " + "sys_permission" + " set ids_id = \'" + componentName + "\' where name = \'" + names + "\'";
            int execute = SqlExecutor.execute(connection, sql);

            //int count = SqlExecutor.execute(connection, "UPDATE "+"sys_permission"+ " set component_name = ? where name = ? ", 0, 0);
            //UPDATE sys_permission SET component_name = "TGMTYY1601_09EH_31_HMRCSAPP9"  WHERE sys_permission.`name`="广角相机UDP下行数据";
            //   log.info("影响行数：{}", count);
            //可以生成sql语句，也可以直接执行update 语句
            // 建议生成sql
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ds.close();
        }
    }

}
